﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.Common;

namespace Kevin.Framework.Data
{
    /// <summary>
    /// sql辅助类
    /// </summary>
    public sealed class DBSqlHelper
    {

        static Database _db;
        private static Database DB
        {
            get
            {
                if (_db == null)
                {
                    string defaultConnectionKey=System.Configuration.ConfigurationManager.AppSettings["DefaultConnectionKey"].ToString();
                    _db = DatabaseFactory.Create(defaultConnectionKey);
                }
                return _db;
            }
        }
        /// <summary>
        /// 执行无返回值Sql语句
        /// </summary>
        /// <param name="sql">sql语句,查询参数请以冒号为前导</param>
        /// <param name="paraValues">参数值</param>
        public static void ExecuteNonQuery(string sql,  params object[] paraValues)
        {
            string strSql = "";
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":");
            DbCommand command = DB.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            DB.ExecuteNonQuery(command);
           
        }
        /// <summary>
        /// 执行特定连接的sql语句
        /// </summary>
        /// <param name="connectionKey">连接字符串</param>
        /// <param name="sql">sql语句,查询参数请以冒号为前导</param>
        /// <param name="paraValues">参数值</param>
        public static void ExecuteNonQueryAt(string connectionKey, string sql, params object[] paraValues)
        {
            string strSql = "";
            Database db = DatabaseFactory.Create(connectionKey);
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":",db);
            
            DbCommand command = db.CreateCommandByCommandType(CommandType.Text, sql, paras);
            db.ExecuteNonQuery(command);
        }

        

        public static DataTable ExecuteDataTableAt(string connectionKey, string sql, params object[] paraValues)
        {
            string strSql = "";
            Database db = DatabaseFactory.Create(connectionKey);
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":", db);
            DbCommand command = db.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            return db.ExecuteDataTable(command);
        }

        public static DataTable ExecuteDataTable(string sql,  params object[] paraValues)
        {
            string strSql = "";
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":");
            DbCommand command = DB.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            return DB.ExecuteDataTable(command);
        }
        public static DataTable ExecuteDataTable(string sql,int pageSize,int pageIndex,out int recordCount, params object[] paraValues)
        {
            recordCount = 0;
            string strSql = "";
            string strCountSql = "";
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":");
            CreateCountSqlFromSql(strSql, out strCountSql);
            strSql = DB.GetPageSpliteSql(strSql, pageSize, pageIndex);
            
            DbCommand commandCount = DB.CreateCommandByCommandType(CommandType.Text, strCountSql, paras);
            int.TryParse(DB.ExecuteScalar(commandCount).ToString(), out recordCount);
            //IDbDataParameter[] p = paras.Clone();
            commandCount.Parameters.Clear();
            DbCommand command = DB.CreateCommandByCommandType(CommandType.Text, strSql, paras);

            return DB.ExecuteDataTable(command);
        }
        public static DbDataReader ExecuteReaderAt(string connectionKey,string sql, params object[] paraValues)
        {
            string strSql = "";
            Database db = DatabaseFactory.Create(connectionKey);
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":", db);
            DbCommand command = db.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            return db.ExecuteReader(command);
        }

        public static DbDataReader ExecuteReader(string sql,  params object[] paraValues)
        {
            string strSql = "";
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":");
            DbCommand command = DB.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            return DB.ExecuteReader(command);
        }
        /// <summary>
        /// 返回单值
        /// </summary>
        /// <param name="connectionKey"></param>
        /// <param name="sql"></param>
        /// <param name="paraValues"></param>
        /// <returns></returns>
        public static object ExecuteScalarAt(string connectionKey, string sql, params object[] paraValues)
        {
            string strSql = "";
            Database db = DatabaseFactory.Create(connectionKey);
            IDbDataParameter[] paras = CreateParametersFromSql(sql, out strSql, paraValues, ":", db);
            DbCommand command = db.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            return db.ExecuteScalar(command);
        }
        /// <summary>
        /// 返回单值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paraValues"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql,  params object[] paraValues)
        {
            string strSql = "";
            IDbDataParameter[] paras = CreateParametersFromSql(sql,out strSql, paraValues, ":");
            DbCommand command = DB.CreateCommandByCommandType(CommandType.Text, strSql, paras);
            return DB.ExecuteScalar(command);
        }

        #region Helper

        private static void CreateCountSqlFromSql(string targetSql, out string resultSql)
        {
            resultSql = "";
            Regex regex = new Regex(@"select[\w\W]+from", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            MatchCollection matchs = regex.Matches(targetSql);
            if (matchs.Count == 1)
            {
                resultSql = targetSql.Replace(matchs[0].Value, "SELECT COUNT(0) FROM ");
            }
            //return resultSql;
        }

        private static IDbDataParameter[] CreateParametersFromSql(string targetSql,out string resultSql, object[] paraValues, string parameterFix,Database db=null)
        {
            resultSql = targetSql;
            

            List<IDbDataParameter> paras = new List<IDbDataParameter>();

            if (paraValues == null) return paras.ToArray();

            Regex regex = new Regex(string.Format(@"{0}[a-z,A-Z,0-9]+", parameterFix), RegexOptions.IgnoreCase | RegexOptions.Multiline);
            MatchCollection matchs = regex.Matches(targetSql);
            if (matchs.Count != paraValues.Length)
            {
                throw new Exception("参数个数与值的个数不匹配！");
            }
            if (db == null)
            {
                db = DB;
            }
            resultSql = targetSql;
            for (int i = 0; i < matchs.Count; i++)
            {
                IDbDataParameter para = db.CreateParameter(new Builder.SqlClauseBuilderItemIUW()
                {
                    Data = paraValues[i],
                    DataField = matchs[i].Value.Replace(parameterFix, "").Replace(" ", "")
                });
                paras.Add(para);
                resultSql = resultSql.Replace(matchs[i].Value, para.ParameterName);
            }
            return paras.ToArray();
        }

        #endregion
    }
}
